package com.xiaoluo.lottery.dao;

import com.xiaoluo.lottery.entity.PlayRecord;
import com.xiaoluo.lottery.filter.PlayRecordFilter;
import com.xiaoluo.lottery.vo.DataPage;
import com.xl.tool.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Caedmon on 2016/4/5.
 */
@Component
public class PlayRecordJdbcDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private RowMapper<PlayRecord> rowMapper=new RowMapper<PlayRecord>() {
        @Override
        public PlayRecord mapRow(ResultSet resultSet, int i) throws SQLException {
            PlayRecord record=new PlayRecord();
            record.setRoundId(resultSet.getLong("round_id"));
            record.setPlayerOpenId(resultSet.getString("player_open_id"));
            record.setPlayerNo(resultSet.getString("player_no"));
            record.setPlayerHeadImage(resultSet.getString("player_head_image"));
            record.setPlayerNickName(resultSet.getString("player_nick_name"));
            record.setCreateAt(resultSet.getTimestamp("create_at"));
            record.setAmount(resultSet.getDouble("amount"));
            record.setIp(resultSet.getString("ip"));
            record.setNum(resultSet.getInt("num"));
            return record;
        }
    };
    public int insert(PlayRecord... records){
        StringBuilder sql=new StringBuilder("INSERT INTO lottery_play_record ")
        .append("(round_id,player_open_id,player_nick_name,player_head_image,player_no,create_at,amount,ip,num) VALUES ");
        int i=0;
        for(PlayRecord record:records){
            if(record==null){
                continue;
            }
            StringBuilder element=new StringBuilder(" (");
            element.append("'").append(record.getRoundId()).append("','")
            .append(record.getPlayerOpenId()).append("','")
            .append(record.getPlayerNickName()).append("','")
            .append(record.getPlayerHeadImage()).append("','")
            .append(record.getPlayerNo()).append("','")
            .append(DateUtil.formateDateToString(record.getCreateAt(),"yyyy-MM-dd HH:mm:ss")).append("',")
            .append(record.getAmount()).append(",'").append(record.getIp()).append("',")
            .append(record.getNum()).append(")");
            if(i<records.length-1){
                element.append(",");
            }
            i++;
            sql.append(element);
        }
        return jdbcTemplate.update(sql.toString());
    }
    public DataPage<PlayRecord> findRecords(PlayRecordFilter filter){
        StringBuilder query=new StringBuilder("SELECT * FROM lottery_play_record ");
        StringBuilder count=new StringBuilder("SELECT count(*) FROM lottery_play_record ");
        StringBuilder condition=new StringBuilder(" where 1=1 ");
        if(!StringUtils.isEmpty(filter.getOpenId())){
            condition.append(" and player_open_id ='").append(filter.getOpenId()).append("'");
        }
        if(filter.getRoundId()!=null){
            condition.append(" and round_id=").append(filter.getRoundId());
        }

        query.append(condition);
        count.append(condition);
        query.append(" ORDER BY create_at DESC");
        query.append(" limit ").append(filter.getStart()).append(",").append(filter.getEnd());

        long total=jdbcTemplate.queryForObject(count.toString(),Long.class);
        List<PlayRecord> records=jdbcTemplate.query(query.toString(),rowMapper);
        return new DataPage<>(records,total,filter.getPageIndex(),filter.getPageSize());
    }
    public List<PlayRecord> findRecordsByRound(long roundId){
        String sql="SELECT * FROM lottery_play_record where round_id ="+roundId;
        return jdbcTemplate.query(sql,rowMapper);
    }
}
